package com.beinfo.contract.dao;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import com.beinfo.contract.model.Contract;
import com.beinfo.contract.model.User;
import com.beinfo.contract.util.C3P0Util;
public class ContractDao {
	
	    public List readFirstTitle(){
	        List<Contract> list =new ArrayList<Contract>();
	        Connection con=null;
	        PreparedStatement psmt=null;
	        ResultSet rs=null;
	        try {
	            Class.forName("com.mysql.jdbc.Driver");
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	        }
	        
	        try {
	            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contract","root","root");
	            String sql="select * from t_contract";
	            psmt=con.prepareStatement(sql);
	            rs=psmt.executeQuery();
	            while(rs.next())
	            {
	            	Contract t1=new Contract();
	            	t1.setCon_id(rs.getInt("con_id"));
	            	t1.setCon_name(rs.getString("con_name"));
	            	t1.setCon_state(rs.getInt("con_state"));
	            	t1.setCon_drafter(rs.getString("con_drafter"));
	            	t1.setCon_approver(rs.getString("con_approver"));
	            	t1.setSigin_date(rs.getString("sigin_date"));
	            	t1.setClosing_date(rs.getString("closing_date"));
	            	t1.setCon_amount(rs.getString("con_amount"));
	            	t1.setCon_pay(rs.getString("con_pay"));
	            	t1.setCon_nonpay(rs.getString("con_nonpay"));
	                list.add(t1);
	            }
	            
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }finally
	        {
	            try {
	                if(rs!=null)
	                {
	                    rs.close();
	                }
	                if(psmt!=null)
	                {
	                    psmt.close();
	                }
	                if(con!=null)
	                {
	                    con.close();
	                }
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return list;
	    }
	    public List daoqiTitle(){
	        List<Contract> list =new ArrayList<Contract>();
	        Connection con=null;
	        PreparedStatement psmt=null;
	        ResultSet rs=null;
	        try {
	            Class.forName("com.mysql.jdbc.Driver");
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	        }
	        
	        try {
	            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contract","root","root");
	        	String sql="SELECT * FROM t_contract WHERE DATE_FORMAT(closing_date,'%Y-%m-%d')>'2017-06-15' AND DATE_FORMAT(closing_date,'%Y-%m-%d')<'2017-06-25'";
	            psmt=con.prepareStatement(sql);
	            rs=psmt.executeQuery();
	            while(rs.next())
	            {
	            	Contract t1=new Contract();
	            	t1.setCon_id(rs.getInt("con_id"));
	            	t1.setCon_name(rs.getString("con_name"));
	            	t1.setCon_state(rs.getInt("con_state"));
	            	t1.setCon_drafter(rs.getString("con_drafter"));
	            	t1.setCon_approver(rs.getString("con_approver"));
	            	t1.setSigin_date(rs.getString("sigin_date"));
	            	t1.setClosing_date(rs.getString("closing_date"));
	            	t1.setCon_amount(rs.getString("con_amount"));
	            	t1.setCon_pay(rs.getString("con_pay"));
	            	t1.setCon_nonpay(rs.getString("con_nonpay"));
	                list.add(t1);
	            }
	            
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }finally
	        {
	            try {
	                if(rs!=null)
	                {
	                    rs.close();
	                }
	                if(psmt!=null)
	                {
	                    psmt.close();
	                }
	                if(con!=null)
	                {
	                    con.close();
	                }
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return list;
	    }
	    
	    public Contract getidbycnoid(int con_id){
	        Connection con=null;
	        PreparedStatement psmt=null;
	        ResultSet rs=null;
	        try {
	            Class.forName("com.mysql.jdbc.Driver");
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	        }
	        
	        try {
	            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contract","root","root");
	            String sql="select * from t_contract where con_id="+con_id+";";
	            psmt=con.prepareStatement(sql);
	            rs=psmt.executeQuery();
	            while(rs.next())
	            {
	            	Contract t1=new Contract();
	            	t1.setCon_id(rs.getInt("con_id"));
	            	t1.setCon_name(rs.getString("con_name"));
	            	t1.setCon_state(rs.getInt("con_state"));
	            	t1.setCon_drafter(rs.getString("con_drafter"));
	            	t1.setCon_approver(rs.getString("con_approver"));
	            	t1.setSigin_date(rs.getString("sigin_date"));
	            	t1.setClosing_date(rs.getString("closing_date"));
	            	t1.setCon_amount(rs.getString("con_amount"));
	            	t1.setCon_pay(rs.getString("con_pay"));
	            	t1.setCon_nonpay(rs.getString("con_nonpay"));
	                return t1;
	            }
	            
	        } catch (SQLException e) {
	            e.printStackTrace();
	            return null;
	        }
			return null;
	    }
	}

